﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace hello_accinv
{
    class Database
    {
        private string dbServer  = "localhost";
        private string dbName    = "accinv";
        private string dbUser    = "root";
        private string dbPwd     = "";
        private string dbCharSet = "utf8";

        private MySqlConnection conn;
        private MySqlCommand cmd;
        private MySqlCommandBuilder cmdBuilder;
        private MySqlDataAdapter da;

        public Database()
        {
            string connString = "Server=" + dbServer + ";" +
                                "Database=" + dbName + ";" +
                                "Uid=" + dbUser + ";" +
                                "Pwd=" + dbPwd + ";" +
                                "CharSet=" + dbCharSet + ";" +
                                "Allow Zero Datetime=True;";
            conn = new MySqlConnection(connString);
            cmd  = new MySqlCommand();
            cmd.Connection = conn;
        }

        public bool TestConnection()
        {
            bool connected;
            try
            {
                conn.Open();
                connected = true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message, "MySQL Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                connected = false;
            }
            finally
            {
                conn.Close();
            }
            return connected;
        }

        public DataTable Select(string value, string table, string condition)
        {
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                da = new MySqlDataAdapter("SELECT " + value + " FROM " + table + " WHERE " + condition, conn);
                cmdBuilder = new MySqlCommandBuilder(da);
                da.Fill(dt);
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Database.Select: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public void Insert(string table, string value)
        {
            try
            {
                conn.Open();
                cmd.CommandText = "INSERT INTO " + table + " VALUES(" + value + ")";
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Database.Insert: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public void Delete(string table, string condition)
        {
            try
            {
                conn.Open();
                cmd.CommandText = "DELETE FROM " + table + " WHERE " + condition;
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Database.Delete: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public void Update(string table, string value, string condition)
        {
            try
            {
                conn.Open();
                cmd.CommandText = "UPDATE " + table + " SET " + value + " WHERE " + condition;
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Database.Update: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public void Query(string query)
        {
            try
            {
                conn.Open();
                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Database.Query: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
